package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import metier.*;

public class SeeJDBC extends See {

    private final static String ID = "ID_See";
    private final static String TYPEENSEIGNEMENT = "TypeEnseignement";
    private final static String MATIERE = "Matiere";
    private final static String NOMBREHEURES = "Nombre_Heure";
    private final static String NOMTABLE = "SEE";
    private final static String updateQuery = "update "
            + NOMTABLE + " set "
            + TYPEENSEIGNEMENT + " = ?, "
            + MATIERE + " = ?, "
            + NOMBREHEURES + " = ? where "
            + ID + " = ?";
    private final static String selectQuery = "select * from "
            + NOMTABLE;
    private final static String selectQuerySemestre = "select * from "
            + NOMTABLE + " se, "
            + MatiereJDBC.getNomTable() + " mat, "
            + UEJDBC.getNomTable() + " u where se."
            + MATIERE + " = mat."
            + MatiereJDBC.getIdCL() + " and mat."
            + MatiereJDBC.getUECL() + " = u."
            + UEJDBC.getIdCL() + " and u."
            + UEJDBC.getSemestreCL() + " = ?";
    private final static String loadQuery = "select * from "
            + NOMTABLE + " where "
            + ID + " = ?";
    private final static String deleteQuery = "DELETE FROM "
            + NOMTABLE + " WHERE "
            + ID + "= ?";
    private final static String idQuery = "SELECT seq_see.nextval  FROM dual";
    private final static String insertQuery = "insert into " + NOMTABLE + " values(?, ?, ?, ?)";

    static String getMatiereCL() {
        return MATIERE;
    }

    static String getIdCL() {
        return ID;
    }

    protected SeeJDBC(int id) {
        super(id);
    }

    public SeeJDBC(Matiere matiere, TypeEnseignement typeEnseignement, int nombreHeures) {
        super(matiere, typeEnseignement, nombreHeures);
    }

    public SeeJDBC() {
        super();
    }

    protected int createId() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next()) {
                throw new ExceptionMetier("creating new id failed due to non generated id");
            }
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }

    /**
     *
     * @param id identifiant du SEE
     */
    public static ArrayList<ElementListe> select() throws ExceptionMetier {
        try {
            ArrayList<ElementListe> mesSee = new ArrayList<ElementListe>();
            ResultSet rset = null;
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement();
            rset = sta.executeQuery(selectQuery);
            while (rset.next()) {
                TypeEnseignement te = new TypeEnseignementJDBC();
                Matiere mat = new MatiereJDBC();
                te.load(rset.getInt(TYPEENSEIGNEMENT));
                mat.load(rset.getInt(MATIERE));
                String nom = mat.getNom() + ","
                        + te.getNom()
                        + ": "
                        + rset.getInt(NOMBREHEURES)
                        + "h";
                mesSee.add(new ElementListe(rset.getInt(ID), nom));
            }
            rset.close();
            sta.close();
            return mesSee;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }

    public static ArrayList<ElementListe> selectSemestre(int idSemestre) throws ExceptionMetier {
        try {
            ArrayList<ElementListe> mesSee = new ArrayList<ElementListe>();
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(selectQuerySemestre);
            psta.setInt(1, idSemestre);
            ResultSet rset = psta.executeQuery();
            while (rset.next()) {
                TypeEnseignement te = new TypeEnseignementJDBC();
                Matiere mat = new MatiereJDBC();
                te.load(rset.getInt(TYPEENSEIGNEMENT));
                mat.load(rset.getInt(MATIERE));
                String nom = mat.getNom() + ","
                        + te.getNom()
                        + ": "
                        + rset.getInt(NOMBREHEURES)
                        + "h";
                mesSee.add(new ElementListe(rset.getInt(ID), nom));
            }
            rset.close();
            psta.close();
            return mesSee;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }

    public void insert() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement insertEnseignant = con.prepareStatement(insertQuery);
                setId(createId());
            insertEnseignant.setInt(1, this.getId());
            insertEnseignant.setInt(2, this.getTypeEnseignement().getId());
            insertEnseignant.setInt(3, this.getMatiere().getId());
            insertEnseignant.setInt(4, this.getNombreHeures());
            insertEnseignant.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed", ex);
        }
    }

    /**
     * M�thode qui permet de mettre � jour l'objet dans le syst�me de stockage
     */
    @Override
    public void update() throws ExceptionMetier {
        try {

            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement updateSee = con.prepareStatement(updateQuery);

            updateSee.setInt(1, getTypeEnseignement().getId());
            updateSee.setInt(2, getMatiere().getId());
            updateSee.setInt(3, getNombreHeures());
            updateSee.setInt(4, getId());
            int res = updateSee.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }

    /**
     * M�thode qui permet de supprimer l'objet dans le syst�me de stockage
     */
    @Override
    public void delete() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement sta = con.prepareStatement(deleteQuery);
            sta.setInt(1, getId());
            sta.executeUpdate();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    public void load(int id) throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement psta = con.prepareStatement(loadQuery);
            psta.setInt(1, id);
            ResultSet rset = psta.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (rset.getInt(ID) != id) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            setId(id);
            TypeEnseignement te = new TypeEnseignementJDBC();
            Matiere mat = new MatiereJDBC();
            te.load(rset.getInt(TYPEENSEIGNEMENT));
            mat.load(rset.getInt(MATIERE));
            setTypeEnseignement(te);
            setMatiere(mat);
            setNombreHeures(rset.getInt(NOMBREHEURES));
            rset.close();
            psta.close();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed", ex);
        }
    }

    public static String getNomTable() {
        return NOMTABLE;
    }
}
